const Koa = require('koa'),
    router = require('koa-router')(),
    render = require('koa-art-template')
    path = require('path'),
    static = require('koa-static'),
    admin = require('./routers/admin'),
    login = require('./routers/login')
    koaBody = require('koa-body'),
    session = require('koa-session'),
        fs = require('fs'),
        mysql = require('mysql'),
    msql = require('./lib/sql');

const app = new Koa();



// 创建公共属性global 类型window
global.host='http://localhost:2022'

// 中间件  
// 配置art-template   所有模板文件放入view下
render(app, {
    root: path.join(__dirname, 'view'),
    extname: '.html',
    debug: process.env.NODE_ENV !== 'production'
});

// 配置静态资源
app.use(static('static'));

// 配置koa-body接受前端的请求体数据
app.use(koaBody({
    multipart: true
}));

// 配置session    cooklie中的key值
app.keys = ['some secret hurr'];

const CONFIG = {
    key: 'koa.sess',
    /** (string) cookie key (default is koa.sess) */
    /** (number || 'session') maxAge in ms (default is 1 days) */
    /** 'session' will result in a cookie that expires when session/browser is closed */
    /** Warning: If a session cookie is stolen, this cookie will never expire */
    // 默认有效期1天
    maxAge: 86400000,

    autoCommit: true,
    /** (boolean) automatically commit headers (default true) */
    //     能不能写
    overwrite: true,
    /** (boolean) can overwrite or not (default true) */
    //   cookie是否只有服务端可以访问
    httpOnly: true,
    /** (boolean) httpOnly or not (default true) */
    //   签名
    signed: true,
    /** (boolean) signed or not (default true) */
    //   
    rolling: false,
    /** (boolean) Force a session identifier cookie to be set on every response. The expiration is reset to the original maxAge, resetting the expiration countdown. (default is false) */
    //是否可以覆盖，到期前进行访问，更新到期时间
    renew: false,
    /** (boolean) renew session when session is nearly expired, so we can always keep user logged in. (default is false)*/
    //   使用https进行访问 ，需要改成http改成false
    secure: false,
    /** (boolean) secure cookie*/
    sameSite: null,
    /** (string) session cookie sameSite options (default null, don't set it) */
};

app.use(session(CONFIG, app));
// or if you prefer all default config, just use => app.use(session(app));


// 路由
//404页面不存在（中间件）

app.use(async (ctx, next) => {
    await next();
    if (ctx.status === 404) {
        ctx.body = "抱歉，页面不存在";
    }
})

//根
router.get('/', ctx => {
    // 模板文件名
    ctx.render("home");
})


router.use("/admin", admin);
// 验证登录
router.use('/login',login)


// 连接数据库 并进行数据的增、删、改、查
router.get('/sql', ctx => {
    // 1.安装并引入mysql包
    // 2.创建数据库连接池
    const pool = mysql.createPool({ host: "localhost", port:'3306', user:'root',password: 'root',database: 'myshop'});
    // 3.连接数据库
    pool.getConnection((err, connection) => {
        if (err) {
            console.log('数据库连接失败');
            console.log(err);
        } else {
            // 1.新增数据create
            // let sql = `INSERT INTO user (username,sex,mobile,name,height,descript) VALUES ('宝宝',0,18255478654,'一一',180,'一一的描述') `;
            // let sql2 = `INSERT INTO user SET username='jack',height=175`;
            // 一次插入多条记录
            // let sql3 = `INSERT INTO user (name,height) VALUES ('张三',180),('李四',168),('王五',188)`
            // // 执行语句
            // connection.query(sql3, (err, res) => {
            //     if (err) {
            //         console.log(err);
            //     } else {
            //         console.log(res);
            //         if (res.affectedRows > 0) {
            //             console.log('执行成功');
            //         }
            //         /* res返回值
            //             OkPacket {
            //             fieldCount: 0,
            //             affectedRows: 1,
            //             insertId: 3,
            //             serverStatus: 2,
            //             warningCount: 0,
            //             message: '',
            //             protocol41: true,
            //             changedRows: 0
            //             }
            //         */
            //     } 
            // })

            // 2.修改数据 update
            // let sql = `UPDATE user SET username='Jerry' WHERE id=4`;
            // 多条数据修改
            // let sql = `UPDATE user SET username='小白',sex=0 WHERE id>4 AND id<7`;
            // 不加where限制会全部修改
            // let sql = `UPDATE user SET pwd=123456`;
            // let sql = `UPDATE user SET height=height+1 WHERE username='小白' `;
            // // 执行语句
            // connection.query(sql, (err, res) => {
            //     if (err) {
            //         console.log(err);
            //     } else {
            //         console.log(res);
            //         if (res.affectedRows > 0) {
            //             console.log("修改成功!");
            //         }
            //     }
            // })


            // 3.删除 id>7  8 9 10 11 12 再进行添加时从id=13开始添加
            // let sql = `DELETE FROM user WHERE id>7`
            // connection.query(sql, (err, res) => {
            //     if (err) {
            //         console.log(err);
            //     } else {
            //         console.log(res);
            //         if (res.affectedRows > 0) {
            //             console.log('删除成功');
            //         }
            //     }
            // })



            // 4.清空表
            // let sql = `truncate user_copy`;
            // let sql = `DROP table user_copy`;
            // connection.query(sql, (err, res) => {
            //     if (err) {
            //         console.log(err);
            //     } else {
            //         console.log(res);
            //     }
            // })


            // 5.查询数据
            // 5.1 查询全部字段(不建议使用*号)调试时使用
            // let sql = `SELECT * FROM user`;
            // let sql = `SELECT id,username,pwd,photo,name,mobile,sex,height,descript FROM user`

            // 5.2 查询特定的字段
            // let sql = `SELECT id,name FROM user`;
            
            // 5.3 条件限制语句 
            // let sql = `SELECT id,username FROM user WHERE (id=1 OR id>5)AND id>6`
            
            // 条件语句between
            // let sql = `SELECT id,name FROM user WHERE height BETWEEN 170 AND 180`;//where height>=170 and height<=180
            
            // 条件语句in
            // let sql = `SELECT id,username FROM user WHERE id IN (13,2,4,7,25,100)`
            
            // 排序
            // let sql = `SELECT id,username FROM user WHERE id IN (13,2,4,7,25,100) ORDER BY field (id,13,2,4,7,25,100)`
            // let sql = `SELECT id,username FROM user WHERE id NOT IN (13,2,4,7,25,100) ORDER BY field (id,13,2,4,7,25,100)`
            
            // 条件语句 模糊查询 like  %代表通配符  %j% 代表前面或者后面有j就可以  j% 表示以j开头的
            // let sql = `SELECT  id,username FROM user WHERE username LIKE 'J%' AND name LIKE '%张%' AND id>10`
            
            // 条件语句正则表达式regexp  注意点：不需要写//   要使用'${reg}'加分号
            // let reg = '^[a-zA-Z]{6,}'
            // let sql = `SELECT id,username FROM user WHERE username REGEXP '${reg}'`

            // 5.4使用别名 多表查询中说
            // let sql = `SELECT id,username,name FROM user WHERE id>0`

            // 5.5排序(ASC 正序 DESC 倒序)
            // let sql = `SELECT id,username,name FROM user ORDER BY id DESC`
            
            // 按照身高排序  身高一样怎么排序
            // let sql = `SELECT id,username,height FROM user ORDER BY height DESC,id DESC`

            //5.6 限制返回的条数(分页基本原理)  找到数据  进行排序  限制返回条数
            // 页面  开始读取的位置  每页条数
            //  1          0            2
            //  2          2            2
            //  3          4            2
            //  4          6            2
            //limit 开始读取的位置=(页码-1)*每次条数，每次读取的条数
            // let sql = `SELECT id,username FROM user WHERE id>0 ORDER BY id DESC LIMIT 2,2`


            //分组和合计函数（去重）
            // let sql = `SELECT uid,sum(counts) as total FROM orders GROUP BY uid ORDER BY total DESC`

            // 什么产品卖的最好
            // let sql = `SELECT product,sum(counts) as total FROM orders GROUP BY product`;
            
            // 销量大于30的产品
            // let sql = `SELECT product,sum(counts) as total FROM orders GROUP BY product HAVING total>30`
            
            // 用户id大于1的 产品统计
            // let sql = `SELECT uid, product,sum(counts) as total FROM orders WHERE uid>1 GROUP BY product HAVING total>15 ORDER BY total ASC LIMIT 0,10`
            
            // 统计多少条数据
            // let sql = `SELECT count(*) as total FROM orders`//7条数据
            
            // 作业：每个用户一次买的最多的一个
            // let sql = `SELECT  id,uid,product,max(counts) as maxcounts FROM orders GROUP BY uid `


            // 多表查询 用户名 用户id      订单id  产品名称  购买数量 
            
            // 内关联(inner join 获取交集)  orders的uid和user的id关联的   
            // let sql = `SELECT user.id as uid,orders.id as oid,username,product,counts FROM user INNER JOIN orders ON (orders.uid=user.id)`
            
            //左关联(left join 以左边为主表)  列出所有用户购买的产品的数量
            // let sql = `SELECT u.id,username,counts FROM user as u LEFT JOIN orders as o ON(o.uid=u.id) `
            
            //右关联(right join 以右边为主表)
            // let sql = `SELECT u.id,username,counts FROM user as u RIGHT JOIN orders as o ON (o.uid=u.id)`
            
            // 练习
            // let sql = `SELECT u.id,username,counts,title,price FROM user as u INNER JOIN orders as o ON (u.id=o.uid) INNER JOIN product as p ON (o.product=p.title) WHERE counts>10`
            
            // 别名(表或字段重新命名) 简化方式
            // let sql = `SELECT u.id as uid,o.id as oid,username,product,counts FROM user as u INNER JOIN orders as o ON (o.uid=u.id)`

            // 子查询 
            let sql = `SELECT id, username FROM user WHERE id IN (SELECT id FROM product)`

            connection.query(sql, (err, res) => {
                if (err) {
                    console.log(err);
                } else {
                    console.log(res);
                }
            })

        }
    })

})

// 测试sql的封装

router.get('/testsql', async ctx => {
    
    let sql = `SELECT id,username FROM user`;
    let res = await msql.query(sql);
    console.log(res);
})




// 配置路由（建议写在开启服务器的前面）
app
    .use(router.routes())
    .use(router.allowedMethods());

// 开启服务器监听
app.listen(2022, () => {
    console.log("#localhost:2022");
})